package kr.hkit.summary_java.erp.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.hkit.myframework.db.DbUtil;
import kr.hkit.summary_java.erp.dto.Employee;

public class EmployeeDAO implements DaoInterface<Employee> {

	private Connection connection;
	private PreparedStatement pstmt;

	public EmployeeDAO(Connection connection) {
		this.connection = connection;
	}

	/*
	 * empno integer, empname varchar(10) unique, title varchar(10) default
	 * '사원', manager integer, salary integer, dno integer,
	 */
	@Override
	public void insertItem(Employee item) throws SQLException {
		String sql = "insert into employee values(?, ?, ?, ?, ?, ?)";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		pstmt.setString(2, item.getEmpName());
		pstmt.setInt(3, item.getTitle());
		pstmt.setInt(4, item.getManager());
		pstmt.setInt(5, item.getSalary());
		pstmt.setInt(6, item.getDeptNo());

		System.out.println(pstmt);
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}

	@Override
	public void deleteItembyNo(Employee item) throws SQLException {
		String sql = "delete from employee where empno = ?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		System.out.println(pstmt);
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}

	@Override
	public void updateItembyNo(Employee item) throws SQLException {
		String sql = "update employee set empname = ?, title = ?, "
				+ "manager = ?, salary = ?, deptno = ? where empno = ?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, item.getEmpName());
		pstmt.setInt(2, item.getTitle());
		pstmt.setInt(3, item.getManager());
		pstmt.setInt(4, item.getSalary());
		pstmt.setInt(5, item.getDeptNo());
		pstmt.setInt(6, item.getEmpNo());
		System.out.println(pstmt);
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}

	@Override
	public ArrayList<Employee> listItems() throws SQLException {
		ArrayList<Employee> list = new ArrayList<>();
		String sql = "select * from employee";
		pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();// 결과값 포인터
		while (rs.next()) {// 다음 레코드가 있니?
			list.add(new Employee(rs.getInt("empno"), rs.getString("empname"),
					rs.getInt("title"), rs.getInt("manager"), rs
							.getInt("salary"), rs.getInt("deptno")));
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return list;
	}

	@Override
	public Employee searchItem(Employee item) throws SQLException {
		
		System.out.println(connection);
		Employee emp = null;
		String sql = "select empno, empname, title, manager, salary, deptno from employee where empno=?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		ResultSet rs = pstmt.executeQuery();

		System.out.println(pstmt);
		if (rs.next()) {
			emp = new Employee();
			emp.setEmpNo(rs.getInt(1));
			emp.setEmpName(rs.getString(2));
			emp.setTitle(rs.getInt(3));
			emp.setManager(rs.getInt(4));
			emp.setSalary(rs.getInt(5));
			emp.setDeptNo(rs.getInt(6));
		}

		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return emp;
	}
}
